import pymysql.cursors
import sys

sys.setrecursionlimit(1000000)


# 链接数据库
def connect():
    config = {
        'host': '127.0.0.1',
        'user': 'root',
        'password': 'qykj1mysql',
        'db': 'ets',
        'charset': 'utf8'
    }
    connection = pymysql.connect(**config)
    return connection


# 获取数据，传入表名，以及sql语句，获取符合条件的一行
def select_data(table, sql, num):
    connection = connect()
    try:
        sql = "SELECT * FROM %s WHERE %s" % (table, sql)
        cursor = connection.cursor()
        cursor.execute(sql)
        if num == 1:
            result = cursor.fetchone()
            connection.commit()
            return result
        else:
            result = cursor.fetchall()
            connection.commit()
            return result

    finally:
        connection.close()


# 更新数据 传入表名 set语句 where语句
def update_data(table, value, where):
    connection = connect()
    try:
        sql = "UPDATE %s SET %s WHERE %s" % (table, value, where)
        print(sql)
        connection.cursor().execute(sql)
        connection.commit()
    finally:
        connection.close()


# 插入数据
def insert_data(table, field, value):
    connection = connect()
    try:
        sql = 'INSERT INTO ' + table + ' ( ' + field + ' ) VALUES ( ' + value + ' )'
        # print(sql)
        connection.cursor().execute(sql)
        connection.commit()
    finally:
        connection.close()


# 获取总数
def get_total_num(table):
    connection = connect()
    try:
        sql = "SELECT count(*) FROM %s" % table
        cursor = connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        connection.commit()
        return result
    finally:
        connection.close()


# 关闭数据库
def close_db():
    connect().close()
